﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using SQLitePCL;
using Windows.Storage;

namespace FASTControlUI
{
    public static class DataAccess
    {
        static string path = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\FASTControlUI\\Data";
        public static void InitializeDatabase()
        {
            string dbpath = Path.Combine(path, "sqliteSample.db");
            Directory.CreateDirectory(path);
            using (var db = new SqliteConnection($"Filename={dbpath}"))
            {
                db.Open();

                string tableCommand = "CREATE TABLE IF NOT " +
                    "EXISTS MyTable (Primary_Key INTEGER PRIMARY KEY, " +
                    "Text_Entry NVARCHAR(2048) NULL)";

                var createTable = new SqliteCommand(tableCommand, db);

                createTable.ExecuteReader();
            }
        }
        public static void AddData(string inputText)
        {
            string dbpath = Path.Combine(path, "sqliteSample.db");
            using (var db = new SqliteConnection($"Filename={dbpath}"))
            {
                db.Open();

                var insertCommand = new SqliteCommand();
                insertCommand.Connection = db;

                // Use parameterized query to prevent SQL injection attacks
                insertCommand.CommandText = "INSERT INTO MyTable VALUES (NULL, @Entry);";
                insertCommand.Parameters.AddWithValue("@Entry", inputText);
                insertCommand.ExecuteReader();
            }

        }
        public static void DeleteData()
        {
            string dbpath = Path.Combine(path, "sqliteSample.db");
            using (var db = new SqliteConnection($"Filename={dbpath}"))
            {
                db.Open();
                var insertCommand = new SqliteCommand();
                insertCommand.Connection = db;
                // Use parameterized query to prevent SQL injection attacks
                insertCommand.CommandText = "DELETE FROM MyTable WHERE Primary_Key like (select Primary_Key from MyTable ORDER BY Primary_Key DESC LIMIT 1);";
                insertCommand.ExecuteReader();
            }
        }
        public static ListItemData GetLastData()
        {
            var entries = new ListItemData();
            string dbpath = Path.Combine(path, "sqliteSample.db");
            using (var db = new SqliteConnection($"Filename={dbpath}"))
            {
                db.Open();
                var selectCommand = new SqliteCommand
                    ("SELECT * from MyTable ORDER BY Primary_Key DESC LIMIT 1", db);

                SqliteDataReader query = selectCommand.ExecuteReader();

                while (query.Read())
                {
                    entries.Id = query.GetInt32(0); entries.Name = query.GetString(1);
                }
            }
            return entries;
        }
        public static List<ListItemData> GetData()
        {
            var entries = new List<ListItemData>();
            string dbpath = Path.Combine(path, "sqliteSample.db");
            using (var db = new SqliteConnection($"Filename={dbpath}"))
            {
                db.Open();
                var selectCommand = new SqliteCommand
                    ("SELECT * from MyTable", db);

                SqliteDataReader query = selectCommand.ExecuteReader();

                while (query.Read())
                {
                    entries.Add(new ListItemData() { Id = query.GetInt32(0), Name = query.GetString(1) });
                }
            }
            return entries;
        }
    }
}
